#!user/bin/python
# -*- coding:UTF-8 -*-

"""
    服务端的与数据库交互模块。
"""

import mysql.connector


class BayesDao:
    def __init__(self, config=None):
        self.config = config

    def set_config(self, config):
        self.config = config

    def __get_connection(self):
        """
        与数据库得到连接。
        :return:(True/False, connection(连接对象)/error msg)
        """
        try:
            conn = mysql.connector.connect(**self.config)
            print('mysql连接成功！')
            return True, conn
        except mysql.connector.Error as err:
            return False, str(err)

    def __is_number(self, str):
        """
        判断字符串是否为数字，Unicode中各类语言数字都会测试。
        :param str: 传入的字符串
        :return: True/False
        """
        try:
            float(str)
            return True
        except ValueError:
            pass

        try:
            import unicodedata
            unicodedata.numeric(str)
            return True
        except (TypeError,ValueError):
            pass

        return False

    def insert_train_data(self, data):
        """
        向数据库插入bayes训练结果。
        :param data: TrainBayes中训练所得数据,由TrainBayes.get_train_result()返回
        :return:(True/False,"success!"/err msg)
        """
        mul_spam_dict = data[0]['spam_dict']
        mul_ham_dict = data[0]['ham_dict']
        mul_spam_all = data[0]['spam_all']
        mul_ham_all = data[0]['ham_all']

        ber_spam_dict = data[1]['spam_dict']
        ber_ham_dict = data[1]['ham_dict']
        ber_spam_all = data[1]['spam_all']
        ber_ham_all = data[1]['ham_all']

        # 创建表格
        status = self.__get_connection()
        if not status[0]:
            return False, status[1]
        else:
            conn = status[1]
        try:
            cursor = conn.cursor()
            cursor.execute("SET NAMES 'utf8';")
            conn.commit()
            cursor.execute("CREATE TABLE bernoulli_spam "
                           "(`word` VARCHAR(255) NOT NULL PRIMARY KEY, "
                           "`percent` double NOT NULL)DEFAULT CHARSET=utf8;")
            cursor.execute("CREATE TABLE bernoulli_ham "
                           "(`word` VARCHAR(255) NOT NULL PRIMARY KEY, "
                           "`percent` double NOT NULL)DEFAULT CHARSET=utf8;")
            cursor.execute("CREATE TABLE multinomial_spam "
                           "(`word` VARCHAR(255) NOT NULL PRIMARY KEY, "
                           "`percent` double NOT NULL)DEFAULT CHARSET=utf8;")
            cursor.execute("CREATE TABLE multinomial_ham "
                           "(`word` VARCHAR(255) NOT NULL PRIMARY KEY, "
                           "`percent` double NOT NULL)DEFAULT CHARSET=utf8;")
            cursor.execute("CREATE TABLE number_all "
                           "(`key` VARCHAR(255) NOT NULL PRIMARY KEY, "
                           "`value` double NOT NULL)DEFAULT CHARSET=utf8;")
            conn.commit()

            # 数据插入各表：
            for key in mul_spam_dict.keys():
                # 清洗切割错误造成的大于数据库储存长度的数据
                if len(key)>255:
                    continue
                # 清洗数字字符串
                elif self.__is_number(key):
                    continue

                sql = "INSERT INTO multinomial_spam" \
                      "(`word`,`percent`) VALUES (%s,%s);"
                val = (key, mul_spam_dict[key])
                try:
                    cursor.execute(sql, val)
                except Exception as err:
                    continue
            conn.commit()

            for key in mul_ham_dict.keys():
                if len(key) > 255:
                    continue
                elif self.__is_number(key):
                    continue

                sql = "INSERT INTO multinomial_ham" \
                      "(`word`,`percent`) VALUES (%s,%s);"
                val = (key, mul_ham_dict[key])
                try:
                    cursor.execute(sql, val)
                except Exception as err:
                    print(str(err))
                    continue
            conn.commit()

            for key in ber_spam_dict.keys():
                if len(key)>255:
                    continue
                elif self.__is_number(key):
                    continue

                sql = "INSERT INTO bernoulli_spam " \
                      "(`word`,`percent`) VALUES (%s,%s);"
                val = (key, ber_spam_dict[key])
                try:
                    cursor.execute(sql, val)
                except Exception as err:
                    print(str(err))
                    continue
            conn.commit()

            for key in ber_ham_dict.keys():
                if len(key) > 255:
                    continue
                elif self.__is_number(key):
                    continue

                sql = "INSERT INTO bernoulli_ham " \
                      "(`word`,`percent`) VALUES (%s,%s);"
                val = (key, ber_ham_dict[key])
                try:
                    cursor.execute(sql, val)
                except Exception as err:
                    print(str(err))
                    continue
            conn.commit()

            # 插入总和数据
            sql = "INSERT INTO number_all " \
                  "(`key`,`value`) VALUES (%s,%s);"
            val = "mul_spam_all", mul_spam_all
            cursor.execute(sql, val)

            val = "mul_ham_all", mul_ham_all
            cursor.execute(sql, val)

            val = "ber_spam_all", ber_spam_all
            cursor.execute(sql, val)

            val = "ber_ham_all", ber_ham_all
            cursor.execute(sql, val)

            conn.commit()
            cursor.close()
            conn.close()
            return True, "success!"
        except Exception as err:
            return False, str(err)

    def select_email_words(self, words_list):
        """
        接收一封邮件中的所有词汇，并查找词汇对应各算法模型中的参数。
        :param words_list:词汇列表。
        :return:data字典，所有算法参数
        """
        status = self.__get_connection()
        if not status[0]:
            return False, status[1]
        else:
            conn = status[1]
        cursor = conn.cursor()
        multinomail_spam = []
        multinomail_ham = []
        bernoulli_spam = []
        bernoulli_ham = []

        mul_spam_sql = "SELECT `percent` FROM multinomial_spam WHERE `word`= %s;"
        mul_ham_sql = "SELECT `percent` FROM multinomial_ham WHERE `word`=%s;"
        ber_spam_sql = "SELECT `percent` FROM bernoulli_spam WHERE `word`=%s;"
        ber_ham_sql = "SELECT `percent` FROM bernoulli_ham WHERE `word`=%s;"
        print(words_list)
        for word in words_list:
            val = (word,)

            cursor.execute(mul_spam_sql, val)
            percent = cursor.fetchone()
            if percent is not None:
                multinomail_spam.append(percent[0])

            cursor.execute(mul_ham_sql, val)
            percent = cursor.fetchone()
            if percent is not None:
                multinomail_ham.append(percent[0])

            cursor.execute(ber_spam_sql, val)
            percent = cursor.fetchone()
            if percent is not None:
                bernoulli_spam.append(percent[0])

            cursor.execute(ber_ham_sql, val)
            percent = cursor.fetchone()
            if percent is not None:
                bernoulli_ham.append(percent[0])

        number_all_sql = 'SELECT `value` FROM number_all WHERE `key`=%s;'
        val = ('mul_spam_all',)
        cursor.execute(number_all_sql, val)
        mul_spam_all = cursor.fetchone()[0]

        val = ('mul_ham_all',)
        cursor.execute(number_all_sql, val)
        mul_ham_all = cursor.fetchone()[0]

        val = ('ber_spam_all',)
        cursor.execute(number_all_sql, val)
        ber_spam_all = cursor.fetchone()[0]

        val = ('ber_ham_all',)
        cursor.execute(number_all_sql, val)
        ber_ham_all = cursor.fetchone()[0]

        cursor.close()
        conn.close()
        data = {
            "mul_spam_percent": multinomail_spam,
            "mul_ham_percent": multinomail_ham,
            "ber_spam_percent": bernoulli_spam,
            "ber_ham_percent": bernoulli_ham,

            "mul_spam_all": mul_spam_all,
            "mul_ham_all": mul_ham_all,
            "ber_spam_all": ber_spam_all,
            "ber_ham_all": ber_ham_all
        }
        return True, data
